package com;

import org.junit.Test;

import java.sql.*;
/*
*PreparedStatement 作用：1预编译SQL语句并执行 开启预编译 编写url时加上&useServerPrepStmts=true
*                       2预防SQL注入问题 会将"' or 'a' = 'a"转换为 '\'or \'1\' = \'1'
*
*  */
public class JDBCDemo06_PreparedStatement {
    @Test
    //sql注入演示
    public void test01() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db01";
        String user = "root";
        String password = "137867";
        Connection connection = DriverManager.getConnection(url, user, password);
        //用户输入 用户名密码
        String usrname="小潘";
        //String pass="' or '1' = '1";
        String pwd = "' or 'a' = 'a";
        //3编写sql
        //String sql = "select * from tbusr where usr = "+name+" and pass = "+pass+"";
        String sql = "select * from tbusr where usrname='"+usrname+"' and pass='"+pwd+"'";
        //4.执行sql对象
        Statement statement = connection.createStatement();
        //5.执行sql
        ResultSet resultSet = statement.executeQuery(sql);
        //6处理结果
        if(resultSet.next()){
            System.out.println("登录成功！");
            System.out.println(sql);
        }else {
            System.out.println("登录失败！");
        }
        //7关闭资源
        statement.close();
        connection.close();

    }

    @Test
    //使用PreparedStatement来防止sql注入
    public void test02() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db01?characterEncoding=UTF-8";
        String user = "root";
        String password = "137867";
        Connection connection = DriverManager.getConnection(url, user, password);
        //用户输入 用户名密码
        String usrname="小王";
        String pwd = "123";


        //3编写sql

       // String sql = "select * from tbusr where usrname='"+usrname+"' and pass='"+pwd+"'";
        String sql="select * from tbusr where  usrname=? and pass=?";
      //  "select * from tb_user where username = ? and password = ?";
        //4.执行sql对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //设置？的值
        preparedStatement.setString(1,usrname);
        preparedStatement.setString(2,pwd);
        System.out.println(sql);
        //5.执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
        //6处理结果
        if(resultSet.next()){
            System.out.println("登录成功！");
            System.out.println(sql);
        }else {
            System.out.println("登录失败！");
        }
        //7关闭资源
        resultSet.close();
        preparedStatement.close();

        connection.close();

    }
}
